package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import Model.Food;
import utils.DBHelper;

public class FoodDao {
   Connection conn;
   String sql = "";
   PreparedStatement ps;
   ResultSet rs;
   public FoodDao() {
	   conn = DBHelper.getConnection();
   }
   public int getIDByShopName(String shopname) {
	   String sql = "select shop_id from tb_shop where shopname=?";
	   try {
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, shopname);
		ResultSet rs = ps.executeQuery();
		if(rs.next()) {
			return rs.getInt("shop_id");
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	   return 0;
   }
  public int getIDByTypeName(String typename) {
	  String sql = "select type_id from tb_kind where typename=?";
	  PreparedStatement ps;
	try {
		ps = conn.prepareStatement(sql);
		 ps.setString(1, typename);
		 ResultSet rs = ps.executeQuery();
		 if(rs.next()) {
			 return rs.getInt("type_id");
		 }
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return 0;
	 
  }
   public boolean insert(Food food) {
	   System.out.println("type_id:"+food.getType_id());
	   sql = "insert into tb_food(foodname,shop_id,type_id,price,intro,pic,recommand)"
	   		+ " values(?,?,?,?,?,?,?)";
	   
	   
	   try {
		ps = conn.prepareStatement(sql);
		ps.setString(1, food.getFoodname());
		ps.setInt(2, food.getShop_id());
		ps.setInt(3, food.getType_id());
		ps.setDouble(4, food.getPrice());
		ps.setString(5, food.getIntro());
		ps.setString(6, food.getPic());
		ps.setInt(7, food.getRecommand());
		int k = ps.executeUpdate();
		if(k>0)return true;
		else return false;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	   return false;
   }
   
   public ArrayList<Food> getAll(){
	   sql = "select t1.food_id as food_id,t1.foodname as foodname, "
	   		+ "t1.shop_id as shop_id,t2.shopname as shopname,t1.type_id as type_id,"
	   		+ "t3.typename as typename,t1.price as price,t1.intro as intro,"
	   		+ "t1.pic as pic,t1.recommand as recommand"
	   		+ " from tb_food t1,tb_shop t2,tb_kind t3 "
	   		+ "where t1.shop_id = t2.shop_id and "
	   		+ "t1.type_id = t3.type_id and t1.food_id !=1";
	   ArrayList<Food> list = new ArrayList<Food>();
	   try {
		ps = conn.prepareStatement(sql);
		rs = ps.executeQuery();
		while(rs.next()) {
			int food_id = rs.getInt("food_id");
			String foodname = rs.getString("foodname");
			int shop_id = rs.getInt("shop_id");
			String shopname = rs.getString("shopname");
			int type_id = rs.getInt("type_id");
			String typename = rs.getString("typename");
			double price = rs.getDouble("price");
			String intro = rs.getString("intro");
			String pic = rs.getString("pic");
			int recommand = rs.getInt("recommand");
			Food food = new Food(food_id,foodname,shop_id,shopname,type_id,typename,
					price,intro,pic,recommand);
			list.add(food);
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	   return list;
	   
   }
   public Food getFoodById(int id) {
	   sql = "select t1.food_id as food_id,t1.foodname as foodname, "
		   		+ "t1.shop_id as shop_id,t2.shopname as shopname,t1.type_id as type_id,"
		   		+ "t3.typename as typename,t1.price as price,t1.intro as intro,"
		   		+ "t1.pic as pic,t1.recommand as recommand"
		   		+ " from tb_food t1,tb_shop t2,tb_kind t3 "
		   		+ "where t1.shop_id = t2.shop_id and "
		   		+ "t1.type_id = t3.type_id and t1.food_id = ?";
	   try {
		ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		   rs = ps.executeQuery();
		   if(rs.next()) {
			   int food_id = rs.getInt("food_id");
				String foodname = rs.getString("foodname");
				int shop_id = rs.getInt("shop_id");
				String shopname = rs.getString("shopname");
				int type_id = rs.getInt("type_id");
				String typename = rs.getString("typename");
				double price = rs.getDouble("price");
				String intro = rs.getString("intro");
				String pic = rs.getString("pic");
				int recommand = rs.getInt("recommand");
				Food food = new Food(food_id,foodname,shop_id,shopname,type_id,typename,
						price,intro,pic,recommand);
				return food;
		   }
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	   return null;
   }
   public boolean update(Food food) {
	   sql = "update tb_food set foodname=?,shop_id=?,"
	   		+ "type_id=?,price=?,intro=?,pic=?,recommand=? where food_id=?";
	   try {
		ps = conn.prepareStatement(sql);
		ps.setString(1, food.getFoodname());
		ps.setInt(2, food.getShop_id());
		ps.setInt(3, food.getType_id());
		ps.setDouble(4, food.getPrice());
		ps.setString(5, food.getIntro());
		ps.setString(6, food.getPic());
		ps.setInt(7, food.getRecommand());
		ps.setInt(8, food.getFood_id());
		int k = ps.executeUpdate();
		if(k>0)return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	   return false;
   }
   public boolean delete(int id) {
	   sql = "delete from tb_food where food_id=?";
	   try {
		ps = conn.prepareStatement(sql);
		 ps.setInt(1, id);
		 int k = ps.executeUpdate();
		 if(k>0)return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	  return false;
   }
   public ArrayList<Food> getFoodByCondition(String condition,String value) {
	   sql = "select t1.food_id as food_id,t1.foodname as foodname, "
		   		+ "t1.shop_id as shop_id,t2.shopname as shopname,t1.type_id as type_id,"
		   		+ "t3.typename as typename,t1.price as price,t1.intro as intro,"
		   		+ "t1.pic as pic,t1.recommand as recommand"
		   		+ " from tb_food t1,tb_shop t2,tb_kind t3 "
		   		+ "where t1.shop_id = t2.shop_id and "
		   		+ "t1.type_id = t3.type_id and "+condition+" like "+"'%"+value+"%'";
	   
	   if("foodname".equals(condition)) {
		   sql += " and foodname != '无'";
		   System.out.println(sql);
	   }
	  
	   ArrayList<Food> list = new ArrayList<Food>();
	   try {
		ps = conn.prepareStatement(sql);
		rs = ps.executeQuery();
		while(rs.next()) {
			int food_id = rs.getInt("food_id");
			String foodname = rs.getString("foodname");
			int shop_id = rs.getInt("shop_id");
			String shopname = rs.getString("shopname");
			int type_id = rs.getInt("type_id");
			String typename = rs.getString("typename");
			double price = rs.getDouble("price");
			String intro = rs.getString("intro");
			String pic = rs.getString("pic");
			int recommand = rs.getInt("recommand");
			Food food = new Food(food_id,foodname,shop_id,shopname,type_id,typename,
					price,intro,pic,recommand);
			list.add(food);
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	   
	   return list;
   }
   public ArrayList<Food> getFoodByShopId(int id){
	   sql =  sql = "select t1.food_id as food_id,t1.foodname as foodname, "
		   		+ "t1.shop_id as shop_id,t2.shopname as shopname,t1.type_id as type_id,"
		   		+ "t3.typename as typename,t1.price as price,t1.intro as intro,"
		   		+ "t1.pic as pic,t1.recommand as recommand"
		   		+ " from tb_food t1,tb_shop t2,tb_kind t3 "
		   		+ "where t1.shop_id = t2.shop_id and "
		   		+ "t1.type_id = t3.type_id and t1.shop_id =?"; 
	   ArrayList<Food> list = new ArrayList<Food>();
	   try {
		ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		rs = ps.executeQuery();
		while(rs.next()) {
			int food_id = rs.getInt("food_id");
			String foodname = rs.getString("foodname");
			int shop_id = rs.getInt("shop_id");
			String shopname = rs.getString("shopname");
			int type_id = rs.getInt("type_id");
			String typename = rs.getString("typename");
			double price = rs.getDouble("price");
			String intro = rs.getString("intro");
			String pic = rs.getString("pic");
			int recommand = rs.getInt("recommand");
			Food food = new Food(food_id,foodname,shop_id,shopname,type_id,typename,
					price,intro,pic,recommand);
			list.add(food);
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	   
	   return list;
   }
}

